
ALTER      proc sp_create_update_sort_lot_stage (
 @part_number varchar(50), 
 @lot_number varchar(50), 
 @lot_status varchar(50), 
 @qty_in  int, 
 @purchase_order_number varchar(50),
 @date_in varchar(50),
 @lot_stage_name varchar(50),
 @expected_date_out varchar (50),
 @vendor_name varchar(50),
 @qty_out varchar(50),
 @date_out varchar(50)) 
 as
 begin
 	declare @process_step_id int
 	declare @location_id int
 	declare @lot_id int
 	declare @this_lot_stauts varchar(50)
 	declare @normal_cost float
 	declare @vendor_id int
 	declare @purchase_order_id int
 	declare @stage_id int
 	declare @sort_stage_lot_id int
 	declare @fab_process_step_id int
 	declare @fab_lot_id int
 	declare @fab_location_id int
 	declare @sort_lot_id int
 	
 	declare @date_time datetime
 	declare @start_date datetime
 	declare @end_date datetime
 
 	select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and PS_Type_Name = 'SORT' and product.ProcessStep_ID = Process_Step.ProcessStep_ID 
 	select @location_id = Location_ID from Location where L_Type = 'Sort' and L_Vendor_Name = @vendor_name
 
 	if @process_step_id is null -- new Product or Product Name mismatch
 	begin
 		select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name='SORT' and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID and Product_Map_Table.Location_ID = @location_id
 
 		if @process_step_id is null
 		begin
			--commented by chandrasekhar
 			--exec sp_create_update_miscellaneous_lot @part_number, @lot_number, @purchase_order_number, @lot_stage_name, null,
 			--@vendor_name, 'Sort', 'SORT', null, @date_in, @date_out, @expected_date_out,null, null, null,  @qty_in, @qty_out, null, null, @lot_status
 			-- dummy update to avoid SQLException
 			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
 			return
 		end
 	end
 
 	if @process_step_id is not null
 	begin
 		update Miscellaneous_Lot_Table set Status = 'Close' where Part_Number = @part_number and Lot_Number = @lot_number and Vendor_Name = @vendor_name
 	end
  	
 	select @location_id = Location_ID from Location where L_Type = 'Sort' and L_Vendor_Name = @vendor_name
 
 	select @this_lot_stauts = Lot_Current_Status, @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
 	print 'Lot_ID = '+convert(varchar, @lot_id)
 
 
 	-- Get Stage ID
 	select @stage_id = Stage_ID from Sort_Stage_Table where SST_Stage_Name = @lot_stage_name
 	if @stage_id is null
 	begin
 		insert into Sort_Stage_Table (ProcessStep_ID, SST_Stage_Name, Timestamp) values (@process_step_id, @lot_stage_name, CURRENT_TIMESTAMP)
 
 	end
 	
 	select @stage_id = Stage_ID from Sort_Stage_Table where SST_Stage_Name = @lot_stage_name
 
 	declare @temp_varchar varchar(20)
 	set @temp_varchar = convert(varchar, current_timestamp, 101)+' 12:00AM'
  	set @start_date = convert(datetime, @temp_varchar)
 	set @temp_varchar = null
 	set @temp_varchar = convert(varchar, current_timestamp, 101)+' 11:59PM' 
 	set @end_date = convert(datetime, @temp_varchar)
 	 
 	select @sort_lot_id = Lot_ID, @date_time = Timestamp from Sort_Stage_Lot where Lot_ID = @lot_id and Stage_ID=@stage_id and timestamp between @start_date and @end_date
 	if @sort_lot_id is null 
 	begin
 		insert into Sort_Stage_Lot (Lot_ID, Stage_ID,  SSL_Qty_In, SSL_Date_In, Timestamp) values (@lot_id, @stage_id, @qty_in, @date_in, CURRENT_TIMESTAMP)
 	end
 	else
 	begin
 	     if @qty_out is null
 	     begin
 		if @qty_in is not null and @qty_in <> 0
 		begin
 			
 			if @date_time is not null
 				begin
 					if @date_time between @start_date and @end_date
 					begin
 						update Sort_Stage_Lot set Stage_ID=@stage_id, SSL_Qty_In=@qty_in, SSL_Date_In=@date_in, Timestamp=CURRENT_TIMESTAMP where Lot_ID = @sort_lot_id and Stage_ID=@stage_id and timestamp between @start_date and @end_date
 					end
 					/*else
 					begin
 						--commented by chandrasekhar
 						--delete from Sort_Stage_Lot where Lot_ID = @lot_id and Stage_ID=@stage_id
 						insert into Sort_Stage_Lot (Lot_ID, Stage_ID,  SSL_Qty_In, SSL_Date_In, Timestamp) values (@lot_id, @stage_id, @qty_in, @date_in, CURRENT_TIMESTAMP)
 					end*/
 				end
 		end
 		set @date_time = null
 	     end
 	end
 end

